package com.pe.pgn.clubpgn.dao.impl;

import java.math.BigDecimal;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.pe.pgn.clubpgn.common.CLPConstantes;
import com.pe.pgn.clubpgn.dao.ArticuloDao;
import com.pe.pgn.clubpgn.dao.hibernate.GenericDaoHibernate;
import com.pe.pgn.clubpgn.domain.ClpdArticulo;
import com.pe.pgn.clubpgn.domain.beans.BNArticulo;
import com.pe.pgn.clubpgn.util.StringUtil;

@Repository("articuloDao")
public class ArticuloDaoImpl extends GenericDaoHibernate<ClpdArticulo, Long> implements
		ArticuloDao {

	public ArticuloDaoImpl(){
		super(ClpdArticulo.class);
	}

	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> buscarArticulos(String deArticulo,
			String coCodigoArticulo, Long coCategoria, String stArticulo,
			String stManejaStock) {
		
		StringBuffer query = new StringBuffer();	
		query.append("select a.id as id, ");
		query.append("		a.de_articulo as deArticulo, ");
		query.append("		a.co_codigo_articulo as coCodigoArticulo, ");
		query.append("		c.de_categoria as deCategoria, ");
		query.append("		a.st_maneja_stock_punto_canje as stManejaPuntoCanje, ");
		query.append("		a.st_maneja_stock_alm_principal as stManejaStockAlmPrincipal, ");
		query.append("		a.st_no_maneja_stock as stNoManejaStock, ");
		query.append("		a.st_instantaneo_normal as stInstantaneoNormal, ");
		query.append("		a.st_instantaneo_vale as stInstantaneoVale, ");
		query.append("		a.st_articulo as stArticulo ");
		query.append("	from clpd_articulo a join clpb_categoria c on a.co_categoria = c.id ");
		query.append("	where 1 = 1 ");
		
		if(coCategoria.intValue() > 0){
			query.append("		and a.co_categoria = ").append(coCategoria);
		}
		
		if(StringUtils.isNotBlank(deArticulo)){
			query.append("		and upper(trim(a.de_articulo)) like '%").append(deArticulo.trim().toUpperCase()).append("%'");
		}
		
		if(StringUtils.isNotBlank(coCodigoArticulo)){
			query.append("		and upper(trim(a.co_codigo_articulo)) like '%").append(coCodigoArticulo.trim().toUpperCase()).append("%'");
		}
		
		if(StringUtils.isNotBlank(stArticulo) 
				&& !stArticulo.equalsIgnoreCase(CLPConstantes.CARACTER_GUION_NO_ESPACIOS)){
			query.append("		and a.st_articulo = '").append(stArticulo).append("'");
		}
		
		if(StringUtils.isNotBlank(stManejaStock) 
				&& !stManejaStock.equalsIgnoreCase(CLPConstantes.CARACTER_GUION_NO_ESPACIOS)){
			
			if(stManejaStock.equalsIgnoreCase(CLPConstantes.ST_NO_MANEJA_STOCK)){
				query.append("		and a.st_no_maneja_stock = '").append(CLPConstantes.ST_VERDADERO).append("'");
			}
			else if(stManejaStock.equalsIgnoreCase(CLPConstantes.ST_MANEJA_STOCK_PUNTO_CANJE)){
				query.append("		and a.st_maneja_stock_punto_canje = '").append(CLPConstantes.ST_VERDADERO).append("'");
			}
			else if (stManejaStock.equalsIgnoreCase(CLPConstantes.ST_MANEJA_STOCK_ALMACEN_PRINCIPAL)){
				query.append("		and a.st_maneja_stock_alm_principal = '").append(CLPConstantes.ST_VERDADERO).append("'");
			}
			else if (stManejaStock.equalsIgnoreCase(CLPConstantes.ST_PREMIO_INSTANTANEO_NORMAL)){
				query.append("		and a.st_instantaneo_normal = '").append(CLPConstantes.ST_VERDADERO).append("'");
			}
			else if (stManejaStock.equalsIgnoreCase(CLPConstantes.ST_PREMIO_INSTANTANEO_VALE)){
				query.append("		and a.st_instantaneo_vale = '").append(CLPConstantes.ST_VERDADERO).append("'");
			}
		} System.out.println(query.toString()+"articulos");
		
		List<Map<String,Object>> lista = findListOfMaps(query.toString(), null);		
		return lista;
	}
	
	public void eliminarArticulo(Long id) {
		
		remove(id);
		getHibernateTemplate().flush();
	}

	
	public boolean esArticuloConDependencias(Long id) {
		
		boolean tieneDependenciaCatalogo = esArticuloConDependenciasDeCatalogo(id);
		boolean tieneDependenciaDocumento = esArticuloConDependenciasDeDocumentoMovimiento(id);
		boolean tieneDependenciaStock = esArticuloConDependenciasDeStockAlmacen(id);		
		boolean tieneDependiaTransaccion = esArticuloConDependenciasDeTransacciones(id);
		
		if(tieneDependenciaCatalogo || tieneDependenciaDocumento 
				|| tieneDependenciaStock || tieneDependiaTransaccion){
			
			return true;
		}		
		return false;
	}
	
	public boolean esArticuloConDependenciasDeCatalogo(Long id){
		
		StringBuffer query = new StringBuffer();
		query.append("select count(*) ");
		query.append("	from clpr_premio_catalogo ");
		query.append("	where co_premio = ").append(id);
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		return false;
	}
	
	public boolean esArticuloConDependenciasDeDocumentoMovimiento(Long id){
		
		StringBuffer query = new StringBuffer();
		query.append("select count(*) ");
		query.append("	from clpd_documento_mov_articulo  ");
		query.append("	where co_articulo = ").append(id);
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		return false;
	}

	public boolean esArticuloConDependenciasDeStockAlmacen(Long id){
				
		StringBuffer query = new StringBuffer();
		query.append("select count(*) ");
		query.append("	from clpm_stock_almacen_catalogo  ");
		query.append("	where co_articulo = ").append(id);
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		return false;
	}
		
	public boolean esArticuloConDependenciasDeTransacciones(Long id){
		
		StringBuffer query = new StringBuffer();
		query.append("select count(*) ");
		query.append("	from clpm_transaccion_articulo  ");
		query.append("	where co_articulo = ").append(id);
		
		int count = jdbcTemplate.queryForInt(query.toString());
		if(count > 0){
			return true;
		}
		return false;		
	}
	
	public void guardarArticulo(ClpdArticulo articulo) {
		
		if(articulo.getId() == null){
			articulo.setCoUsuarioCreador(getUsuarioLogueado().getUsername());
			articulo.setDaFechaCreacion(Calendar.getInstance());
			
			String codigo = obtenerSiguienteCodigoArticulo(articulo);
			codigo = articulo.getCoCodigoArticulo().concat(codigo);
			articulo.setCoCodigoArticulo(codigo);
			
			articulo.setStArticulo(Boolean.TRUE);
		}
		else{
			articulo.setCoUsuarioModificador(getUsuarioLogueado().getUsername());
			articulo.setDaFechaModificacion(Calendar.getInstance());
		}
		getHibernateTemplate().saveOrUpdate(articulo);
		getHibernateTemplate().flush();
	}

	public ClpdArticulo obtenerArticulo(Long id) {
		
		ClpdArticulo articulo = get(id);		
		return articulo;
	}

	
	@SuppressWarnings("unchecked")
	public List<ClpdArticulo> obtenerArticulos() {
		
		Criteria criteria = DetachedCriteria.forClass(ClpdArticulo.class)
				.getExecutableCriteria(getSessionFactory().getCurrentSession());

		criteria.setMaxResults(CLPConstantes.MAXIMO_RESULTADO_INICIAL);
		criteria.add(Restrictions.eq("stArticulo", Boolean.TRUE));
		criteria.addOrder(Order.desc("id"));
		
		List<ClpdArticulo> lista = criteria.list(); 
		return lista;
	}
	
	public String obtenerSiguienteCodigoArticulo(ClpdArticulo articulo){
		
		StringBuilder query = new StringBuilder();
		query.append("select max(to_number(substr(co_codigo_articulo, 4))) ");
		query.append("	from clpd_articulo where st_articulo = '").append(CLPConstantes.ST_VERDADERO).append("' ");
		query.append(" and co_categoria = ").append(articulo.getClpbCategoria().getId());
		
		BigDecimal object = (BigDecimal)jdbcTemplate.queryForObject(query.toString(), BigDecimal.class);
		String codigo = "";
		if(object == null){
			object = BigDecimal.ONE;			
		}
		else{
			object = object.add(BigDecimal.ONE);
		}
		
		codigo = StringUtil.getStringLeftPad(object.toString(), CLPConstantes.LONGITUD_NUMERO_CODIGO_ARTICULO,
				CLPConstantes.CODIGO_RELLENO_TARJETA_PERSONA, CLPConstantes.ESPACIO_VACIO);
		
		return codigo;
	}
	
	public BNArticulo obtenerArticuloPorID(Long idArticulo) {
		
		BNArticulo bnArticulo= new BNArticulo();
		
		StringBuffer query = new StringBuffer();	
		query.append("select a.id as id, ");
		query.append("		a.de_articulo as deArticulo, ");
		query.append("		a.co_codigo_articulo as coCodigoArticulo, ");
		query.append("		a.co_categoria as coCategoria, ");
		query.append("		a.st_maneja_stock_punto_canje as stManejaPuntoCanje, ");
		query.append("		a.st_maneja_stock_alm_principal as stManejaStockAlmPrincipal, ");
		query.append("		a.st_no_maneja_stock as stNoManejaStock, ");
		query.append("		a.st_instantaneo_normal as stInstantaneoNormal, ");
		query.append("		a.st_instantaneo_vale as stInstantaneoVale, ");
		query.append("		a.st_articulo as stArticulo, ");
		query.append("		a.va_stock_actual as vaStockActual, ");
		query.append("		a.va_stock_disponible as vaStockDisponible, ");
		query.append("		a.st_maneja_serie as stManejaSerie ");
		query.append("	from clpd_articulo a ");
		query.append(" 	where a.id=? ");
		
		List<Map<String, Object>> lista=jdbcTemplate.queryForList(query.toString(),idArticulo.toString());
		if(!lista.isEmpty()){
			Map<String, Object> map = (HashMap<String, Object>)lista.get(0);
			
			bnArticulo.setId(new Long(map.get("id").toString()));
			bnArticulo.setCoArticulo(map.get("coCodigoArticulo").toString());
			bnArticulo.setDeArticulo(map.get("deArticulo").toString());
			bnArticulo.setStManejaStockPuntoCanje(map.get("stManejaPuntoCanje").toString());
			bnArticulo.setStManejaStockAlmPrincipal(map.get("stManejaStockAlmPrincipal").toString());
			bnArticulo.setStNoManejaStock(map.get("stNoManejaStock").toString());
			bnArticulo.setStInstantaneoNormal(map.get("stInstantaneoNormal").toString());
			bnArticulo.setStInstantaneoVale(map.get("stInstantaneoVale").toString());
			
		}
		
		

		return bnArticulo;
	}

}
